﻿
CREATE PROCEDURE [dbo].[amix_ImportVersiuneAnterioara]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint
AS
BEGIN
	DECLARE @strCodUnitate varchar(10)
	DECLARE @AnLunaCurenta int
	DECLARE @UltimaLuna int, @UltimulAn int
	SELECT @AnLunaCurenta = COALESCE(MAX([AnDeLucru]*12+[LunaDeLucru]),2400) FROM [ERP].[dbo].[a_arh_CFGAmix]
	SELECT @UltimaLuna = [LunaDeLucru], @UltimulAn=[AnDeLucru] FROM [ERP].[dbo].[a_arh_CFGAmix]
		WHERE [AnDeLucru]*12+[LunaDeLucru] = @AnLunaCurenta
		
	SET @AnLunaCurenta = @AnLunaCurenta+1	
	IF @UltimaLuna=12
	BEGIN
		SET @UltimulAn = @UltimulAn +1
		SET @UltimaLuna  = 1
	END
	ELSE
		SET @UltimaLuna = @UltimaLuna + 1	
	PRINT(@AnLunaCurenta)

	SET @strCodUnitate = RTRIM(LTRIM(CAST(@CodUnitate as varchar(10))))
	---importa din baza de date ERP
	BEGIN TRAN

    
    DELETE FROM [AmixAnsamble] WHERE IDMFixPrincipal IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE  [CodUnitate] =@CodUnitate) 
	IF @@ERROR != 0 GOTO EROARE	
    
    DELETE FROM [AmixComponenteSecundare] WHERE IDMijlocFix IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE  [CodUnitate] =@CodUnitate) 
	IF @@ERROR != 0 GOTO EROARE	
    
    DELETE FROM [AmixAmortizare] WHERE IDMijlocFix IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE  [CodUnitate] =@CodUnitate)
	IF @@ERROR != 0 GOTO EROARE	
    
    DELETE FROM [AmixRegistruBK] WHERE [CodUnitate]=@CodUnitate 
	IF @@ERROR != 0 GOTO EROARE	

    DELETE FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate 
	IF @@ERROR != 0 GOTO EROARE	

    DELETE FROM [AmixDocumente] WHERE [CodUnitate]=@CodUnitate 
	IF @@ERROR != 0 GOTO EROARE	

    DELETE FROM [AmixCatalog] 
	IF @@ERROR != 0 GOTO EROARE	
	
	PRINT('Sterge Tabele - ok')
	

	INSERT INTO [AmixCatalog]
			   ([Grupa]
			   ,[Subgrupa]
			   ,[Clasa]
			   ,[Subclasa]
			   ,[Familia]
			   ,[DMinAmortizare]
			   ,[DMaxAmortizare]
			   ,[ContImobilizare]
			   ,[ContAmortizare]
			   ,[Denumire]
			   ,[Cod])
		 SELECT  
			   Grupa,
			   Subgrupa, 
			   Clasa, 
			   Subclasa, 
			   Familia, 
			   DMin,
			   DMax,
			   ContImobilizare,
			   ContAmortizare,
			   LTRIM(RTRIM(SUBSTRING([ACTIVE],1,150))) Denumire,
			   LTRIM(RTRIM(CAST(COALESCE(GRUPA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(CAST(COALESCE(SUBGRUPA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(CAST(COALESCE(CLASA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(CAST(COALESCE(SUBCLASA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(COALESCE(FAMILIA,'')))
			   FROM [ERP].[dbo].[a_act_CATALOG] a
			    
	IF @@ERROR != 0 GOTO EROARE					   
	SET IDENTITY_INSERT [AmixCatalog] ON
	INSERT INTO [AmixCatalog]
			   ([IDClasificare],
			    [Grupa]
			   ,[Subgrupa]
			   ,[Clasa]
			   ,[Subclasa]
			   ,[Familia]
			   ,[DMinAmortizare]
			   ,[DMaxAmortizare]
			   ,[ContImobilizare]
			   ,[ContAmortizare]
			   ,[Denumire]
			   ,[Cod])
		VALUES (0,  
			   0,
			   0, 
			   0, 
			   0, 
			   NULL, 
			   0,
			   0,
			   '',
			   '',
			   'Fara Clasa',
			   '0')
	
	SET IDENTITY_INSERT [AmixCatalog] OFF
	PRINT('catalog - ok')
	
	
	
    SET IDENTITY_INSERT [AmixRegistru] ON
   
    
	INSERT INTO [AmixRegistru]
   ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],
    [DataIntrare],
    [DataIesire],
    [CodUnitate],[IDDepartament],
    [IDStare],[ContImobilizare],[ContCheltuiala],
    [DurataAmortizare],[RegimAmortizare],[ValoareInventar],
    [PunereInFunctiune],
    [Compus],[Blocat],[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn])
	SELECT CAST(NRINV AS int),[NRINV],[DEN],LTRIM(RTRIM(CONTS)) +'.'+ LTRIM(RTRIM(CONTAN1)) + '.' +LTRIM(RTRIM(CONTAN2)),[StocFaptic], COALESCE(b.[IDClasificare],0) IDClasificare,
	CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + '-' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) +'-01',
	CAST(CASE WHEN COALESCE(ANO,0)=0 THEN 1900 ELSE ANO END AS VARCHAR(4)) + '-' + CAST(CASE WHEN COALESCE(LNO,0)=0 THEN 1 ELSE LNO END AS VARCHAR(2)) +'-01',
	--DATEADD(month,DURN*12,CAST(CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) +''-01'' AS DATETIME)),
	@CodUnitate,c.[IDDepartament], 
	CASE WHEN CONS='A' THEN 1 WHEN CONS='I' THEN 3 WHEN CONS='E' THEN 5 WHEN CONS='O' THEN 2 ELSE 5 END  [IDStare],NULL,NULL,
	DURN*12,Regim,ValInv,
	DATEADD(month,1,CAST(CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + '-' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) + '-01' AS DATETIME)),
	0,0,1,null,COALESCE(vala,0),CASE WHEN COALESCE(ANO*12+LNO,@AnLunaCurenta)<@AnLunaCurenta  THEN COALESCE(ANO*12+LNO,@AnLunaCurenta) - COALESCE(ANI*12 +LNI,1970*24) ELSE @AnLunaCurenta - (COALESCE(ANI,1970)*12+COALESCE(LNI,0)) END [LuniAmortizate],COALESCE(RA,0),COALESCE(AMOAN,0)
	FROM [ERP].[dbo].[a_act_MIX] a 
	LEFT OUTER JOIN [AmixCatalog] b ON coalesce(b.Grupa,0)=coalesce(a.Grupa,0) AND COALESCE(b.Subgrupa,0)=COALESCE(a.Subgrupa,0) and COALESCE(b.clasa,0)=COALESCE(a.clasa,0) and Coalesce(b.subclasa,0)=coalesce(a.subclasa,0) and coalesce(b.familia,'')=coalesce(a.familia,'') COLLATE  SQL_Latin1_General_CP1_CI_AS
    JOIN [Departamente] c  ON LTRIM(RTRIM(a.SEC)) = LTRIM(RTRIM(c.Cod))     

	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [AmixRegistru] OFF
	PRINT('Import Registru curent - ok')
	COMMIT
	DECLARE doc_cursor CURSOR FOR  SELECT [AnDeLucru],[LunaDeLucru] FROM [ERP].[dbo].[a_arh_CFGAmix]
  	
    declare @LunaLucru smallint
  	declare @AnLucru smallint
    declare @LunaLucruStr varchar(2)
  	declare @AnLucruStr VARCHAR(4)

  
  	OPEN doc_cursor
  	FETCH NEXT FROM doc_cursor INTO @AnLucru,@LunaLucru
  	SET @LunaLucruStr = CAST(@LunaLucru as varchar(2))
  	SET @AnLucruStr = CAST(@AnLucru as varchar(4))
  	IF LEN(@LunaLucruStr)=1 
  		SET @LunaLucruStr = '0' + @LunaLucruStr
  	DECLARE @Comanda varchar(4000) 	
  	WHILE (@@FETCH_STATUS =0)
  	BEGIN		  		
  	

		SET @Comanda = 'INSERT INTO [AmixRegistruBK]
		   ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],
			[DataIntrare],
			[DataIesire],
			[CodUnitate],[IDDepartament],
			[IDStare],[ContImobilizare],[ContCheltuiala],
			[DurataAmortizare],[RegimAmortizare],[ValoareInventar],
			[PunereInFunctiune],
			[Compus],[Blocat],[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[Luna],[An])
			SELECT CAST(NRINV AS int),[NRINV],[DEN],LTRIM(RTRIM(CONTS)) +''.''+ LTRIM(RTRIM(CONTAN1)) + ''.'' +LTRIM(RTRIM(CONTAN2)),[StocFaptic], b.[IDClasificare],
			CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) +''-01'',
			CAST(CASE WHEN COALESCE(ANO,0)=0 THEN 1900 ELSE ANO END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNO,0)=0 THEN 1 ELSE LNO END AS VARCHAR(2)) +''-01'','
			+CAST(@CodUnitate AS varchar(10))+',c.[IDDepartament], 
			CASE WHEN CONS=''A'' THEN 1 WHEN CONS=''I'' THEN 3 WHEN CONS=''E'' THEN 5 WHEN CONS=''O'' THEN 2 ELSE 5 END  [IDStare],NULL,NULL,
			DURN*12,Regim,ValInv,
			DATEADD(month,1,CAST(CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) + ''-01'' AS DATETIME)),
			0,0,1,null,COALESCE(vala,0),CASE WHEN COALESCE(ANO*12+LNO,'+CAST(@AnLunaCurenta AS varchar(10))+')<'+CAST(@AnLunaCurenta AS Varchar(10))+'  THEN COALESCE(ANO*12+LNO,'+
			CAST(@AnLunaCurenta AS Varchar(10)) +') - COALESCE(ANI*12 +LNI,1970*24) ELSE '+CAST(@AnLunaCurenta AS VARCHAR(10))+' - (COALESCE(ANI,1970)*12+COALESCE(LNI,0)) END [LuniAmortizate],COALESCE(RA,0),COALESCE(AMOAN,0),'
			+@lunalucrustr +','+@AnLucruStr +
			' FROM [ERP].[dbo].[a_act_MIX] a 
			JOIN [AmixCatalog] b ON b.Grupa=a.Grupa AND b.Subgrupa=a.Subgrupa and b.clasa=a.clasa and Coalesce(b.subclasa,0)=coalesce(a.subclasa,0) and coalesce(b.familia,'''')=coalesce(a.familia,'''')
			JOIN [Departamente] c  ON a.SEC = c.Cod'     
		PRINT(@Comanda)
		EXEC(@Comanda) 
		IF @@ERROR <>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  				
		
		SET @comanda = 
		'INSERT INTO [AmixAmortizare]
			([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa],[AmortizareAn],[AmortizareLuna],
			[DurataNormata],[LuniAmortizate],[Blocat],[IDBK])
  			SELECT 
  			CAST(NRINV AS int) IDMijlocFix,'+@LunaLucruStr+','+@AnLucruStr+',valinv,COALESCE(vala,0),valinv-COALESCE(vala,0),'+
  			'COALESCE(amoan,0)  AS [AmortizareAn],'+
  			'COALESCE(ra,0) AS [AmortizareLuna],COALESCE(durn,0),('
  			+@AnLucruStr + '*12+' + @LunaLucruStr + ')-COALESCE(ANI*12+LNI,1970*12)' + 
  			'[LuniAmortizate],0,NULL
			FROM  [ERP].[dbo].[a_' + @AnLucruStr + @LunaLucruStr + 'a_arh_MIX] ' + 
			' WHERE CAST(NRINV AS int) IN (SELECT IDMijlocFix FROM [AmixRegistru])'
		print @comanda
  		execute(@comanda) 
  		IF @@ERROR<>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  		
		SET @comanda = 
		'INSERT INTO [AmixDocumente]([NrInventar],[Denumire],[IdClasificare],[IDDepartament],[ContAmortizare],[ValoareInventar],[DurataAmortizare]'+
           ',[RegimAmortizare],[DataDoc],[NumarDoc],[ComisiaRec],[DurExtindere],[CodUnitate],[IDMod],[IDTipDocument],[ContCheltuiala]'+
           ',[ContImobilizare],[Luna],[Anul],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[ContTransfer],[IDMijlocFix],[PunereInFunctiune],[Compus]'+
           ',[AmortizarePrimulAn],[IDGestionar],[ValoareSuplimentara],[LuniAmortizate],[IDBK])'+
		'SELECT REPLACE(NRINV,''.'',''''),COALESCE(DEN,''''),COALESCE(b.[IDClasificare],0),COALESCE(COALESCE(c.[IDDepartament],c1.[IDDepartament]),0),Conts,Coalesce(ValInv,0),COALESCE(DurN*12,0),'+
		'COALESCE(Regim,0),COALESCE(DataDoc,CAST(CAST(An as varchar(4)) + ''-'' + CAST(LUNA AS varchar(2)) + ''-01''  AS DATETIME)),NumarDoc,COALESCE(ComisiaRec,''''),COALESCE(DurExtindere,0),'+@strCodUnitate+',CASE WHEN COALESCE(CDEL,'''')=''C'' THEN 4 ELSE 0 END,Tip,NULL,'+
		'NULL,Luna,An,Coalesce(RA,0),Coalesce(RA*12,0),COALESCE(VALA,0),Conttr,CAST(REPLACE(NRINV,''.'','''') AS INT),DATEADD(month,1,COALESCE(DataDoc,CAST(CAST(An as varchar(4)) + ''-'' + CAST(LUNA AS varchar(2)) + ''-01''  AS DATETIME))),0,'+
		'0,1,0,0,NULL '+
		'FROM  [ERP].[dbo].[a_' + @AnLucruStr + @LunaLucruStr + 'a_arh_MIS] a 
		LEFT OUTER JOIN [AmixCatalog] b ON coalesce(b.Grupa,0)=coalesce(a.Grupa,0) AND coalesce(b.Subgrupa,0)=coalesce(a.Subgrupa,0) and coalesce(b.clasa,0)=coalesce(a.clasa,0) and coalesce(b.subclasa,0)=coalesce(a.subclasa,0) and coalesce(b.familia,'''')=coalesce(a.familia,'''') '           
		+'LEFT OUTER JOIN [Departamente] c  ON LTRIM(RTRIM(a.SECI)) = LTRIM(RTRIM(c.COD))'  
		+'LEFT OUTER JOIN [Departamente] c1  ON LTRIM(RTRIM(a.SECO)) = LTRIM(RTRIM(c1.COD))'  
		
		print @comanda
  		execute(@comanda)
  		IF @@ERROR<>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  		
		
		
		
  		FETCH NEXT FROM doc_cursor INTO @AnLucru,@LunaLucru		
  		SET @LunaLucruStr = CAST(@LunaLucru as varchar(2))
  		SET @AnLucruStr = CAST(@AnLucru as varchar(4))
  		IF LEN(@LunaLucruStr)=1 
  			SET @LunaLucruStr = '0' + @LunaLucruStr  		
  	end ---
  		
	--print 'dealocare'
	CLOSE doc_cursor
	DEALLOCATE doc_cursor

	SET @LunaLucru = @LunaLucru + 1
	IF @LunaLucru=13 
	BEGIN 
		SET @AnLucru=@AnLucru + 1
		SET @LunaLucru = 1
	END		
  	SET @LunaLucruStr = CAST(@LunaLucru as varchar(2))
  	SET @AnLucruStr = CAST(@AnLucru as varchar(4))
  	IF LEN(@LunaLucruStr)=1 
  		SET @LunaLucruStr = '0' + @LunaLucruStr  

		INSERT INTO [AmixAmortizare]
			([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa],[AmortizareAn],[AmortizareLuna],[DurataNormata],[LuniAmortizate],[Blocat],[IDBK])
  			SELECT CAST(NRINV AS int),@LunaLucru ,@AnLucruStr,valinv,vala,valinv-vala,
  			COALESCE(amoan,0)  AS [AmortizareAn],
  			COALESCE(ra,0) AS [AmortizareLuna],COALESCE(durn,0),
  			CASE 
  			   WHEN COALESCE(RA,0)=0 THEN COALESCE(DURN,0)*12 
  			   ELSE 
  			   CASE 
  			      WHEN ROUND(VALA/RA,0)>32767 THEN 32767 
  			      ELSE ROUND(VALA/RA,0) 
  			   END 
  			END [LuniAmortizate],0,NULL
			FROM  [ERP].[dbo].[a_act_MIX]  
			 WHERE CAST(NRINV AS int) IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE RegimAmortizare!=0)
  		IF @@ERROR<>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  		


	UPDATE [AmixSetup] SET [LunaCurenta] = @UltimaLuna, [AnulCurent]=@UltimulAn WHERE 
		[CodUnitate] = @CodUnitate
	

	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END